package ut.data;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class Transaction{
	public static final int RESERVED_DAY = 4; // Now - Finish Time of Task < Reserved Day
	// 交易種類
	public static final int TYPE_EVENT = 9; // 活動所得
	public static final int TYPE_ABUSE_REPORT = 8; // 申訴所得
	public static final int TYPE_ACCEPT_TASK = 7; // 案件成交
	public static final int TYPE_REJECTED_TASK = 6; // 譯者被退件
	public static final int TYPE_REJECT_TASK = 5; // 案主退件
	public static final int TYPE_ABANDON_TASK = 4; // 譯者主動放棄
	public static final int TYPE_REVOKE_TASK = 3; // 案主主動撤回
	public static final int TYPE_DEAD_TASK = 2; // 系統自動撤回，媒合失敗
	public static final int TYPE_DEPOSIT = 1; // 存款
	public static final int TYPE_WITHDRAW = -1; // 提款
	public static final int TYPE_CREATE_TASK = -2; // 建立案件
	public static final int TYPE_ABUSE_REPORTED = -3; // 申訴損失
	// 交易狀態
	public static final int STATE_PROCESSING = 0;
	public static final int STATE_COMPLETED = 1;
	// 儲值
	public static boolean deposit(User user, int money, String paypalTransactionId){
		Connection con = null;
		Statement stmt = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 更新玩家的儲值金額
			stmt.executeUpdate("update user set money=money+" + money + " where id=" + user.id);
			// 加入交易紀錄中
			stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + user.id + "," + money + ",'" + paypalTransactionId + "'," + Transaction.TYPE_DEPOSIT + "," + Transaction.STATE_COMPLETED + ",now())");
			con.commit();
			// 更新 Session 中的物件
			user.money += money;
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 取得剛完成的交易總額 ( 譯者收到款項至少三天後才能取款 )
	public static int getReservedMoney(int id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			// 計算剛完成的交易總額 ( 譯者收到款項至少三天後才能取款 )
			int reservedMoney = 0;
			rs = stmt.executeQuery("select price,state from task where translator=" + id + " and (state=" + Task.ACCEPTED + " or state=" + Task.REJECTED + ") and TO_DAYS(now()) - TO_DAYS(finish_time) < " + Transaction.RESERVED_DAY);
			while(rs.next()){
				if(rs.getInt("state") == Task.ACCEPTED){
					reservedMoney += rs.getInt("price");
				}else{
					reservedMoney += rs.getInt("price") / 4;
				}
			}
			return reservedMoney;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 提款
	public static boolean withdraw(User user, int money, String paypalEmailId){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 計算剛完成的交易總額 ( 譯者收到款項至少三天後才能取款 )
			int reservedMoney = 0;
			rs = stmt.executeQuery("select price,state from task where translator=" + user.id + " and (state=" + Task.ACCEPTED + " or state=" + Task.REJECTED + ") and TO_DAYS(now()) - TO_DAYS(finish_time) < " + Transaction.RESERVED_DAY);
			while(rs.next()){
				if(rs.getInt("state") == Task.ACCEPTED){
					reservedMoney += rs.getInt("price");
				}else{
					reservedMoney += rs.getInt("price") / 4;
				}
			}
			// 剛完成的交易總額不可提領
			if(money > user.money - reservedMoney){
				return false;
			}
			// 更新玩家的儲值金額
			stmt.executeUpdate("update user set money=money-" + money + " where id=" + user.id);
			// 加入交易紀錄中
			stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + user.id + "," + money + ",'" + paypalEmailId + "'," + Transaction.TYPE_WITHDRAW + "," + Transaction.STATE_PROCESSING + ",now())");
			con.commit();
			// 更新 Session 中的物件
			user.money -= money;
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 提款處理完成
	public static boolean withdrawCompleted(long id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 更新交易紀錄狀態
			stmt.executeUpdate("update transaction set state=" + Transaction.STATE_COMPLETED + " where id=" + id + " and state=" + Transaction.STATE_PROCESSING);
			con.commit();
			return true;
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 取得使用者歷史交易紀錄的最大頁數
	public static int getMaxTransactionPage(int id, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from transaction where user=" + id);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得使用者歷史交易紀錄列表
	public static Transaction[] getTransactions(int id, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Transaction[] transactions = new Transaction[pageSize];
			rs = stmt.executeQuery("select * from transaction where user=" + id + " order by time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				transactions[index] = new Transaction(rs.getLong("id"), rs.getInt("user"), rs.getInt("money"), rs.getString("paypal"), rs.getInt("type"), rs.getInt("state"), rs.getTimestamp("time"));
				index++;
			}
			return transactions;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 取得所有歷史交易紀錄的最大頁數 (管理者)
	public static int getMaxTransactionPage(int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from transaction");
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得所有歷史交易紀錄列表 (管理者)
	public static Transaction[] getTransactions(int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Transaction[] transactions = new Transaction[pageSize];
			rs = stmt.executeQuery("select * from transaction order by time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				transactions[index] = new Transaction(rs.getLong("id"), rs.getInt("user"), rs.getInt("money"), rs.getString("paypal"), rs.getInt("type"), rs.getInt("state"), rs.getTimestamp("time"));
				index++;
			}
			return transactions;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	/* Instance Definition */
	public long id;
	public int user;
	public int money;
	public String paypal;
	public int type;
	public int state;
	public Date time;
	public Transaction(long id, int user, int money, String paypal, int type, int state, Date time){
		this.id = id;
		this.user = user;
		this.money = money;
		this.paypal = paypal;
		this.type = type;
		this.state = state;
		this.time = time;
	}
}